package br.unb.persistence;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import br.unb.beans.Empresario;
import br.unb.connection.FactoryConnection;

public class EmpresarioPersistence {
	
	    /*Insere um empresario no Banco de Dados*/
	    public static void insertEmpresario(Empresario empresario) throws SQLException, ClassNotFoundException {
	    	Connection connection = FactoryConnection.getInstance().criaConexao();
			Statement statement = connection.createStatement();
			String SQL_INSERT_CLI = "INSERT INTO empresario(nome,telefone,dataNas,sexo,idade,cargo,empresa,federacao,confederacao,dataIn,dataOut,dataCad)" +
				"values('"+empresario.getNome()+"','"+empresario.getTelefone()+"','"+empresario.getDataNas()+"','"+empresario.getSexo()+"'," +
						"'"+empresario.getIdade()+"','"+empresario.getCargo()+"','"+empresario.getEmpresa()+"','"+empresario.getFederacao()+"'," +
						"'"+empresario.getConfederacao()+"','"+empresario.getDataIn()+"','"+empresario.getDataOut()+"','"+empresario.getDataCad()+"');";
		
			    statement.executeUpdate(SQL_INSERT_CLI);
				System.out.println("EMPRESARIO CADASTRADO COM SUCESSO.");	  
				
			connection.close();
			statement.close();
			
	    }
	    
	    /*Deleta um usuario do Banco*/
	    public static void deleteEmpresario(String cpf) throws SQLException, ClassNotFoundException {
			Connection connection = FactoryConnection.getInstance().criaConexao();
			Statement statement = connection.createStatement();
			String SQL_DELETE = "delete from empresario where cpf = '"+cpf+"';";
		
			    statement.executeUpdate(SQL_DELETE);
				System.out.println("EMPRESARIO DELETADO COM SUCESSO.");
			  
			connection.close();
			statement.close();
	    }
	    
	    /*Seleciona um empresario do Banco pelo seu CPf.*/	    
	    public static Empresario selectByCpf(String cpf) throws SQLException, ClassNotFoundException {
			Connection con = FactoryConnection.getInstance().criaConexao();
			Statement stm = con.createStatement();
			
			Empresario empresario = null;
			
			ResultSet rs = stm.executeQuery("SELECT * FROM empresario WHERE CPF = "+cpf+";");
			
			if(rs.next()) {
				empresario = new Empresario();
				
				empresario.setNome(rs.getString(1));
				empresario.setTelefone(rs.getInt(2));
				empresario.setDataNas(rs.getString(3));
				empresario.setSexo(rs.getString(4));
				empresario.setIdade(rs.getInt(5));				
				empresario.setCargo(rs.getString(6));				
				empresario.setEmpresa(rs.getString(7));
				empresario.setFederacao(rs.getString(8));
				empresario.setConfederacao(rs.getString(9));
				empresario.setDataIn(rs.getString(10));
				empresario.setDataOut(rs.getString(11));
				empresario.setDataCad(rs.getString(12));
				
			}
			
			con.close();
			stm.close();
			
			return empresario;
		}
	    
	    /* Seleciona todos Empresarios */
	    public static ArrayList<Empresario> selectAll() throws SQLException, ClassNotFoundException {
			Connection con = FactoryConnection.getInstance().criaConexao();
			Statement stm = con.createStatement();
			
			ArrayList<Empresario> empresarios = new ArrayList<Empresario>();
			
			ResultSet rs = stm.executeQuery("SELECT * FROM empresario;");
			
			while(rs.next()) {
				Empresario empresario = new Empresario();
				
				empresario.setNome(rs.getString(1));
				empresario.setTelefone(rs.getInt(2));
				empresario.setDataNas(rs.getString(3));
				empresario.setSexo(rs.getString(4));
				empresario.setIdade(rs.getInt(5));				
				empresario.setCargo(rs.getString(6));				
				empresario.setEmpresa(rs.getString(7));
				empresario.setFederacao(rs.getString(8));
				empresario.setConfederacao(rs.getString(9));
				empresario.setDataIn(rs.getString(10));
				empresario.setDataOut(rs.getString(11));
				empresario.setDataCad(rs.getString(12));
				
				empresarios.add(empresario);
			}
			
			return empresarios;
		}
		
		private static String SQL_UPDATE = "update empresario set nome = ?, set telefone ?, set dataNas ?, set sexo ?, set idade ?, set cargo ?, " +
				"set empresa ?, set federacao ?, set confederacao ?, set dataIn ?, set dataOut ?, set dataCad ?  WHERE cpf = ?";
		
		/*Altera empresario no Banco*/
		public static void alteraEmpresario(Empresario empresario)
		  throws ClassNotFoundException, SQLException {
			Connection connection = FactoryConnection.getInstance().criaConexao();
			PreparedStatement ps = connection.prepareStatement(SQL_UPDATE);
			
			ps.setString(1, empresario.getNome());			
			ps.setInt(2, empresario.getTelefone());
			ps.setString(3, empresario.getDataNas());	
			ps.setString(4, empresario.getSexo());	
			ps.setInt(5, empresario.getIdade());
			ps.setString(6, empresario.getCargo());	
			ps.setString(7, empresario.getEmpresa());	
			ps.setString(8, empresario.getFederacao());	
			ps.setString(9, empresario.getConfederacao());	
			ps.setString(10, empresario.getDataIn());	
			ps.setString(11, empresario.getDataOut());	
			ps.setString(12, empresario.getDataCad());	
			
			ps.executeUpdate();
			
			connection.close();
			ps.close();
			
		}
	    
}